import pymysql
import json
import utils
import re


# 违规数据表格 数据入mysql库
def json_insert_violation_data_table():
    results = []
    f = open("Violation_data_table.json", "r", encoding="utf-8")
    for line in f.readlines():
        results.append(json.loads(line))
    mysql_insert_violation_data_table_mysql(results)

# 违规数据表格 功能屏蔽数据入mysql库
def json_insert_violation_sealing_data_table():
    results = []
    f = open("Violation_Sealing_table.json", "r", encoding="utf-8")
    for line in f.readlines():
        results.append(json.loads(line))
    mysql_insert_sealing_violation_mysql(results)

# 违规表格 数据入库
def mysql_insert_violation_data_table_mysql(results):
    conn = pymysql.Connect(
        host='',  # mysql服务器地址
        port=3306,  # mysql服务器端口号
        user='',  # 用户名
        password='',  # 密码
        db='Real_time_Update_violation',  # 数据库名
        charset='utf8mb4'  # 连接编码
    )
    cursor = conn.cursor()
    i = 0
    for result in results:
        sql_select_mysql = """SELECT * FROM `update_time_violation_info`
                                WHERE (`时间`='{0}') AND (`原始ID`='{1}') AND (`违规内容`='{2}')""".format(
            result["时间"], result["原始ID"], result["违规内容"])
        # 执行语句
        effect_row = cursor.execute(sql_select_mysql)
        # 事务提交，否则数据库得不到更新
        conn.commit()
        if effect_row == 1:
            print("获取的违规数据 mysql库已存在")
            pass
        else:
            try:
                sql_insert = """INSERT INTO `update_time_violation_info`(`时间`, `名称`, `微信号`, `原始ID`,
                 `粉丝来源`, `类型`, `性别`, `累计关注`, `违规类型`, `违规内容`, `违规处理`, `是否封禁`, `主体公司`)VALUES (
                '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}', '{9}','{10}','{11}','{12}')""".format(
                    result["时间"],  result["名称"], result["微信号"], result["原始ID"], result["粉丝来源"],
                    result["类型"], result["性别"], result["累计关注"], result["违规类型"], result["违规内容"],
                    result["违规处理"], result["是否封禁"], result["主体公司"])
                # 执行语句
                cursor.execute(sql_insert)
                # 事务提交，否则数据库得不到更新
                conn.commit()
                i += 1
                print("违规表格 数据入mysql库 {0}条".format(i))
            except (ValueError, TypeError, SystemError, UnicodeEncodeError, SyntaxError):
                # Rollback in case there is any error
                conn.rollback()
    cursor.close()
    conn.close()

# 违规表格 筛选屏蔽功能 数据入库
def mysql_insert_sealing_violation_mysql(results):
    conn = pymysql.Connect(
        host='',  # mysql服务器地址
        port=3306,  # mysql服务器端口号
        user='',  # 用户名
        password='',  # 密码
        db='Real_time_Update_violation',  # 数据库名
        charset='utf8mb4'  # 连接编码
    )
    cursor = conn.cursor()
    i = 0
    for result in results:
        sql_select_mysql = """SELECT * FROM `Violation_Sealing_function_table`
                                WHERE (`时间`='{0}') AND (`原始ID`='{1}')""".format(result["时间"], result["原始ID"])
        # 执行语句
        effect_row = cursor.execute(sql_select_mysql)
        # 事务提交，否则数据库得不到更新
        conn.commit()
        if effect_row == 1:
            try:
                sql_update_mysql = """
                                UPDATE `Violation_Sealing_function_table` SET `时间`="{0}", `名称`="{1}", `微信号`="{2}",
                                `原始ID`="{3}", `粉丝来源`="{4}", `类型`="{5}", `性别`="{6}", `累计关注`="{7}",
                                `自定义菜单`="{8}", `自动回复`="{9}", `文章被搜索`="{10}", `群发文本`="{11}",
                                `文章分享到朋友圈可见`="{12}", `模版消息`="{13}", `客服消息`="{14}", `callback`="{15}",
                                `阅读原文`="{16}", `流量主`="{17}", `被搜索`="{18}", `被关注`="{19}", `广告主`="{20}",
                                `消息群发`="{21}", `从公众号打开网页`="{22}" WHERE (`时间`="{0}") AND (`原始ID`="{3}")
                """.format(
                    result["时间"], result["名称"], result["微信号"], result["原始ID"], result["粉丝来源"],
                    result["类型"], result["性别"], result["累计关注"], result["自定义菜单"], result["自动回复"],
                    result["文章被搜索"], result["群发文本"], result["文章分享到朋友圈可见"], result["模版消息"], result["客服消息"],
                    result["callback"], result["阅读原文"], result["流量主"], result["被搜索"], result["被关注"],
                    result["广告主"], result["消息群发"], result["从公众号打开网页"]
                )
                # 执行语句
                cursor.execute(sql_update_mysql)
                # 事务提交，否则数据库得不到更新
                conn.commit()
                i += 1
                print("违规表格 功能封禁数据更新 {0}条".format(i))
            except (ValueError, TypeError, SystemError, UnicodeEncodeError, SyntaxError):
                # Rollback in case there is any error
                conn.rollback()
        else:
            try:
                sql_insert = """INSERT INTO Violation_Sealing_function_table(`时间`, `名称`, `微信号`, `原始ID`,
                 `粉丝来源`, `类型`, `性别`, `累计关注`, `自定义菜单`, `自动回复`, `文章被搜索`, `群发文本`, `文章分享到朋友圈可见`,
                 `模版消息`, `客服消息`, `callback`, `阅读原文`, `流量主`, `被搜索`, `被关注`, `广告主`, 
                 `消息群发`, `从公众号打开网页`) VALUES (
                '{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}',
                '{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}')""".format(
                    result["时间"],  result["名称"], result["微信号"], result["原始ID"], result["粉丝来源"],
                    result["类型"], result["性别"], result["累计关注"], result["自定义菜单"], result["自动回复"],
                    result["文章被搜索"], result["群发文本"], result["文章分享到朋友圈可见"], result["模版消息"],
                    result["客服消息"], result["callback"], result["阅读原文"], result["流量主"], result["被搜索"],
                    result["被关注"], result["广告主"], result["消息群发"], result["从公众号打开网页"])
                # 执行语句
                cursor.execute(sql_insert)
                # 事务提交，否则数据库得不到更新
                conn.commit()
                print("违规表格 功能封禁数据 写入mysql库 ")
            except (ValueError, TypeError, SystemError, UnicodeEncodeError, SyntaxError):
                # Rollback in case there is any error
                conn.rollback()
    cursor.close()
    conn.close()


# 违规表格 写入json文件
def violation_data_table(data_date, account, type_of_violation, irregularities, violation_treatment,
                         whether_or_not_to_ban, accumulative_attention):
    info = {
        "时间": data_date,
        "名称": account['nickname'],
        "微信号": account['wx_id'],
        "原始ID": account['gh_id'],
        "粉丝来源": account['fans_src'],
        "类型": account['type'],
        "性别": account['gender'],
        "累计关注": accumulative_attention,
        "违规类型": type_of_violation,
        "违规内容": irregularities,
        "违规处理": violation_treatment,
        "是否封禁": whether_or_not_to_ban,
        "主体公司": account['gs']
    }
    with open('Violation_data_table.json', 'a', encoding='utf-8') as f:
            f.write(json.dumps(info)+"\r")

# 违规表格 封禁功能 写入json文件
def violation_sealing_data_table(data_date, account, custom_menu, automatic_recovery, articles_are_searched,
                                 group_sending_text, accumulative_attention, friendship_circle, template_message,
                                 customer_service_news, callback, read_the_original_text, traffic_master,
                                 searched, concerned_about, advertiser, mass_of_news, open_web):
    info = {
        "时间": data_date,
        "名称": account['nickname'],
        "微信号": account['wx_id'],
        "原始ID": account['gh_id'],
        "粉丝来源": account['fans_src'],
        "类型": account['type'],
        "性别": account['gender'],
        "累计关注": accumulative_attention,
        "自定义菜单": custom_menu,
        "自动回复": automatic_recovery,
        "文章被搜索": articles_are_searched,
        "群发文本": group_sending_text,
        "文章分享到朋友圈可见": friendship_circle,
        "模版消息": template_message,
        "客服消息": customer_service_news,
        "callback": callback,
        "阅读原文": read_the_original_text,
        "流量主": traffic_master,
        "被搜索": searched,
        "被关注": concerned_about,
        "广告主": advertiser,
        "消息群发": mass_of_news,
        "从公众号打开网页": open_web
    }
    with open('Violation_Sealing_table.json', 'a', encoding='utf-8') as f:
            f.write(json.dumps(info)+"\r")

def get_violation_info(item):
    title = item['Title']
    violation_content = re.sub('<.*?>', '', item['Content'])
    violation_cause = ''
    violation_title = ''
    violation_result = ''
    if re.search('违规处理', title):
        if re.search('涉[嫌及](.+?)，', violation_content):
            violation_cause = re.search('涉[嫌及](.+?)，', violation_content).group(1)
            violation_title = re.search(
                '违规(.*?)消息“(.*?)”', violation_content).group(2)
        else:
            violation_cause = re.search('发现此公众号(.+?)，', violation_content).group(1)
            violation_title = re.search(
                '违规(.*?)消息“(.*?)”', violation_content).group(2)
    elif re.search('功能屏蔽', title):
        if re.search('涉[嫌及](.+?)，', violation_content):
            violation_cause = re.search(
                '涉[嫌及](.+?)，', violation_content).group(1)
        elif re.search('您的帐号经查(.+?)，', violation_content):
            violation_cause = re.search(
                '您的帐号经查(.+?)，', violation_content).group(1)
        if re.search('违规内容：(.+?)如有异议', violation_content):
            violation_title = re.sub('&nbsp;', '', re.search(
                '违规内容：(.+?)如有异议', violation_content).group(1))
        elif re.search('流量主通过不正当方式制造虚假或无效曝光量、点击量', violation_content):
            violation_title = re.search(
                '流量主通过不正当方式制造虚假或无效曝光量、点击量', violation_content).group(0)
        features_by_date = {}
        features = re.findall('已屏蔽([^功能]+?)功能至([^,，]+)[，,]', violation_content)
        for feature in features:
            feature_name = feature[0]
            feature_date = feature[1]
            if feature_date in features_by_date:
                features_by_date[feature_date].append(feature_name)
            else:
                features_by_date[feature_date] = [feature_name]
        for feature_date, forbidden_features in features_by_date.items():
            violation_result += "{}: {}\n".format(
                feature_date, ','.join(forbidden_features))
    elif re.search('粉丝删除', title):
        violation_cause = re.search('所禁止的(.+?)，', violation_content).group(1)
        violation_title = re.sub('&nbsp;', '', re.search(
            '发起的"(.*?)"', violation_content).group(1))
        violation_result = re.search('已删除.+?粉丝数。', violation_content).group(0)
    elif re.search('责令处理', title):
        violation_title = re.search('图文消息“(.*?)”', violation_content).group(1)
        violation_result = re.search(
            '已被互联网信息内容主管部门责令删除', violation_content).group(0)
    return violation_cause, violation_title, violation_result


def get_violation_sealing_info(item):
    title = item['Title']
    violation_content = re.sub('<.*?>', '', item['Content'])
    if re.search('功能屏蔽', title):
        features = re.findall('已屏蔽([^功能]+?)功能至([^,，]+)[，,]', violation_content)
        return features
    else:
        return False


def get_violation_list(data_date, gh_id):
    return utils.get_json_file(gh_id, data_date, 'violation-list.json', data_type=list)
